SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[SP_TMDOCU_PROV_Q12]  
/*-------------------------------------------------------*/  
/*---Empresa             : OFISIS S.A.                 --*/  
/*---Cliente             : MANUELA RAMOS               --*/  
/*---Sistema             : Presupuesto                 --*/  
/*---M½dulo              : Empresa                     --*/  
/*---Programa            : Pagos Proyectados por dias  --*/  
/*---      y semanas         --*/  
/*---Script              : TMDOCP_GENE_Q11.sql         --*/  
/*---Nombre SP           : SP_TMDOCP_GENE_Q11          --*/  
/*---Desarrollado por    : Merardo Antonio Rojas Mendoza-*/  
/*---Fecha Creaci½n      : 07/09/1999                  --*/  
/*---Base Datos          : Microsoft Sql Server        --*/  
/*---Versi½n             : 7.0                         --*/  
/*---Invoca a SP         :                             --*/  
/*-------------------------------------------------------*/  
  
/*-------------------------------------------------------*/  
/*---Modificado 1 por     : Daniel Chiang        --*/  
/*---Fecha Modificaci½n   : 07/04/2000         --*/   
/*---Detalle Modificaci½n :          --*/  
/*-------------------------------------------------------*/  
  
/*-------------------------------------------------------*/  
/*---Modificado 2 por     : Denis Villacr‚s        --*/  
/*---Fecha Modificaci½n   : 14/07/2000         --*/   
/*---Detalle Modificaci½n : Se Agreg¢ la variable.     --*/  
/*       @ISTI_FECH          --*/  
/*-------------------------------------------------------*/  
  
/*-------------------------------------------------------*/  
/*---Comentarios                                       --*/  
/*---                                                  --*/  
/*---                                                  --*/  
/*-------------------------------------------------------*/  
  
/*-------------------------------------------------------*/  
/* DROP PROC SP_TMDOCP_GENE_Q11     */  
/* GRANT ALL ON SP_TMDOCP_GENE_Q11 TO PUBLIC     */  
/* Execute SP_TMDOCU_PROV_Q11 '2003/04/23', 'DIA', 'V', 'D',  "And TMDOCP_GENE.CO_PROV >= '00000000001' And TMDOCP_GENE.CO_PROV <= '00000000011' And TMDOCP_GENE.CO_TIPO_DOCU in ( 'LET')"  */  
/*-------------------------------------------------------*/  
  
@IDFE_VENC  TD_DT_001,  
@ISTI_OPER  TD_VC_003,  
@ISTI_FECH  TD_VC_001,  
@ISTI_REPO TD_VC_001,  
@ISCA_WHER_LOCA VARCHAR(500)  
  
  
As  
DECLARE  
@VSCO_EMPR_INIC  TD_VC_002,  
@VSCO_EMPR_FINA  TD_VC_002,  
@VSCO_PROV_INIC  TD_VC_020,  
@VSCO_PROV_FINA  TD_VC_020,  
@VSCO_MONE_INIC  TD_VC_003,  
@VSCO_MONE_FINA  TD_VC_003,  
@VSDE_FECH  VarChar(30),  
@VSFE_VENC  VarChar(30)  
  
CREATE TABLE #TABLA(CO_EMPR CHAR(2),  
      CO_PROV VARCHAR(20),   
      CO_MONE CHAR(3),  
       CO_ESTA_DOCU VARCHAR(3),  
       DE_NOMB VARCHAR(100),  
       DE_MONE VARCHAR(50),  
       NO_AUXI VARCHAR(100),  
       CO_TIPO_DOCU VARCHAR(3),  
       NU_DOCU_PROV VARCHAR(20),  
       IM_0001 NUMERIC(16,4),  
       IM_0002 NUMERIC(16,4),  
       IM_0003 NUMERIC(16,4),  
       IM_0004 NUMERIC(16,4),  
       IM_0005 NUMERIC(16,4),  
       IM_0006 NUMERIC(16,4),  
       IM_0007 NUMERIC(16,4),  
       IM_0008 NUMERIC(16,4),  
       IM_0009 NUMERIC(16,4),  
       IM_0010 NUMERIC(16,4),  
       IM_0011 NUMERIC(16,4),  
       IM_0012 NUMERIC(16,4),  
       IM_0013 NUMERIC(16,4),  
       IM_0014 NUMERIC(16,4),  
       IM_0015 NUMERIC(16,4),  
       IM_SALD NUMERIC(16,4) DEFAULT 0)  
  
  
  
/* SELECCION POR FECHA DE VENCIMIENTO */  
  
IF @ISTI_FECH = 'V'   
SELECT @VSDE_FECH = 'TMDOCP_GENE.FE_VENC'  
  
IF @ISTI_FECH = 'P'   
--SELECT @VSDE_FECH = 'TMDOCP_GENE.FE_PROG_PAGO'  
SELECT @VSDE_FECH = 'T8.FE_PROG'  
  
  
SELECT @VSFE_VENC = Convert(VarChar(10),@IDFE_VENC,103)  
  
  
IF @ISTI_REPO='D'  
BEGIN  
IF @ISTI_OPER = 'DIA'  
 BEGIN  
  
EXECUTE (' INSERT INTO #TABLA (CO_EMPR , CO_MONE, CO_PROV, CO_ESTA_DOCU, DE_NOMB, DE_MONE, NO_AUXI, CO_TIPO_DOCU, NU_DOCU_PROV,  
       IM_0001, IM_0002, IM_0003, IM_0004, IM_0005, IM_0006,   
       IM_0007, IM_0008, IM_0009, IM_0010, IM_0011, IM_0012,   
       IM_0013, IM_0014, IM_0015 )  
 SELECT   
  TMDOCP_GENE.CO_EMPR ,  
  TMDOCP_GENE.CO_MONE ,  
  TMDOCP_GENE.CO_PROV ,  
  TMDOCP_GENE.CO_ESTA_DOCU,  
  T1.DE_NOMB,   
  T2.DE_MONE,  
  T3.NO_AUXI,  
  TMDOCP_GENE.CO_TIPO_DOCU,  
 TMDOCP_GENE.NU_DOCU_PROV,  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "0")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "1")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "2")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "3")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "4")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "5")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "6")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "7")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "8")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "9")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "10")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "11")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "12")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "13")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "14")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))  
 FROM    TMDOCP_GENE, TMEMPR T1, TTMONE T2, TMAUXI_EMPR T3, TTDOCU_CNTB T5, TDPROG_PAGO T8, TMPARA_TESO T9  
 WHERE   1 = 1 '+@ISCA_WHER_LOCA+'  
   And   '+@VSDE_FECH+'  >= Convert(DateTime,"'+@VSFE_VENC+'",103)  
   And   '+@VSDE_FECH+'  < Dateadd(day,16,Convert(DateTime,"'+@VSFE_VENC+'",103))  
   And   (TMDOCP_GENE.CO_ESTA_DOCU = "ACT"  
   Or     TMDOCP_GENE.CO_ESTA_DOCU = "ACE")  
   And   ( T9.ST_APRO_CXPA  = "S"  
           And  T8.TI_SITU  = "APR"  
   or    
  ( T9.ST_APRO_CXPA  = "N"  
  And  T8.TI_SITU != "PAG"))  
   And  T1.CO_EMPR   = TMDOCP_GENE.CO_EMPR  
   And  TMDOCP_GENE.CO_MONE = T2.CO_MONE  
   And  TMDOCP_GENE.CO_EMPR = T3.CO_EMPR  
   And   T3.TI_AUXI_EMPR  = "P"  
   And  T3.CO_AUXI_EMPR =  TMDOCP_GENE.CO_PROV  
   And  TMDOCP_GENE.CO_TIPO_DOCU = T5.TI_DOCU  
   And   TMDOCP_GENE.CO_EMPR = T8.CO_EMPR  
   And   TMDOCP_GENE.CO_PROV = T8.CO_PROV  
   And   TMDOCP_GENE.CO_TIPO_DOCU = T8.CO_TIPO_DOCU  
   And   TMDOCP_GENE.NU_DOCU_PROV = T8.NU_DOCU_PROV  
   And   TMDOCP_GENE.CO_EMPR = T9.CO_EMPR  
  
 ORDER BY  1 , 2, 3')  
  
END   
  
/*OPCION DE SEMANAS*/  
  
IF @ISTI_OPER = 'SEM'  
BEGIN   
  
  
EXECUTE (' INSERT INTO #TABLA (CO_EMPR , CO_MONE, CO_PROV, CO_ESTA_DOCU, DE_NOMB, DE_MONE, NO_AUXI, CO_TIPO_DOCU, NU_DOCU_PROV,  
       IM_0001, IM_0002, IM_0003, IM_0004, IM_0005, IM_0006,   
       IM_0007, IM_0008, IM_0009, IM_0010, IM_0011, IM_0012,  
       IM_0013, IM_0014, IM_0015 )  
 SELECT    
  TMDOCP_GENE.CO_EMPR ,  
  TMDOCP_GENE.CO_MONE ,  
  TMDOCP_GENE.CO_PROV ,  
  TMDOCP_GENE.CO_ESTA_DOCU,  
  T1.DE_NOMB,  
  T2.DE_MONE,  
  T3.NO_AUXI ,  
   TMDOCP_GENE.CO_TIPO_DOCU,  
               TMDOCP_GENE.NU_DOCU_PROV,  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "0")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "1")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "2")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "3")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "4")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "5")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "6")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "7")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "8")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "9")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "10")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "11")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "12")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "13")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")),  
  PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "14")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))  
 From  TMDOCP_GENE, TMEMPR  T1, TTMONE T2 , TMAUXI_EMPR T3,  TTDOCU_CNTB T5, TDPROG_PAGO T8, TMPARA_TESO T9  
 where  1 = 1 '+@ISCA_WHER_LOCA+'  
   And  '+@VSDE_FECH+' >= Convert(DateTime,"'+@VSFE_VENC+'",103)  
   And  '+@VSDE_FECH+' < dateadd(day,100,Convert(DateTime,"'+@VSFE_VENC+'",103))  
   And   (TMDOCP_GENE.CO_ESTA_DOCU = "ACT"  
   Or   TMDOCP_GENE.CO_ESTA_DOCU = "ACE")  
   And   ( T9.ST_APRO_CXPA  = "S"  
           And  T8.TI_SITU  = "APR"  
   or    ( T9.ST_APRO_CXPA  = "N"  
           And  T8.TI_SITU != "PAG"))  
   And T1.CO_EMPR  =  TMDOCP_GENE.CO_EMPR  
   And TMDOCP_GENE.CO_MONE =  T2.CO_MONE  
   And TMDOCP_GENE.CO_EMPR =  T3.CO_EMPR  
   And    T3.TI_AUXI_EMPR = "P"  
   And T3.CO_AUXI_EMPR =  TMDOCP_GENE.CO_PROV  
  AND  TMDOCP_GENE.CO_TIPO_DOCU = T5.TI_DOCU  
   AND   TMDOCP_GENE.CO_EMPR = T8.CO_EMPR  
   AND   TMDOCP_GENE.CO_PROV = T8.CO_PROV  
   AND   TMDOCP_GENE.CO_TIPO_DOCU = T8.CO_TIPO_DOCU  
   AND   TMDOCP_GENE.NU_DOCU_PROV = T8.NU_DOCU_PROV  
   And   TMDOCP_GENE.CO_EMPR = T9.CO_EMPR  
ORDER BY 1, 2, 3')  
  
END  
END  
  
  
ELSE  
BEGIN  
IF @ISTI_OPER = 'DIA'  
 BEGIN  
EXECUTE (' INSERT INTO #TABLA (CO_EMPR , CO_MONE, CO_PROV, CO_ESTA_DOCU, DE_NOMB, DE_MONE, NO_AUXI,  
       IM_0001, IM_0002, IM_0003, IM_0004, IM_0005, IM_0006,   
       IM_0007, IM_0008, IM_0009, IM_0010, IM_0011, IM_0012,  
       IM_0013, IM_0014, IM_0015 )  
 SELECT   
  TMDOCP_GENE.CO_EMPR ,  
  TMDOCP_GENE.CO_MONE ,  
  TMDOCP_GENE.CO_PROV ,  
  MAX(TMDOCP_GENE.CO_ESTA_DOCU),  
  MAX(T1.DE_NOMB),   
  MAX(T2.DE_MONE),  
  MAX(T3.NO_AUXI),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "0")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "1")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "2")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "3")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "4")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "5")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "6")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "7")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "8")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')),  "9")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "10")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "11")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "12")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "13")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) ),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( DAY, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "14")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")) )  
 FROM    TMDOCP_GENE, TMEMPR  T1 , TTMONE T2 , TMAUXI_EMPR T3 , TTDOCU_CNTB T5, TDPROG_PAGO T8, TMPARA_TESO T9  
 WHERE   1 = 1 '+@ISCA_WHER_LOCA+'  
   And   '+@VSDE_FECH+'  >= Convert(DateTime,"'+@VSFE_VENC+'",103)  
   And   '+@VSDE_FECH+'  < Dateadd(day,16,Convert(DateTime,"'+@VSFE_VENC+'",103))  
   And   (TMDOCP_GENE.CO_ESTA_DOCU = "ACT"  
   Or   TMDOCP_GENE.CO_ESTA_DOCU = "ACE")  
   And   ( T9.ST_APRO_CXPA  = "S"  
           And  T8.TI_SITU  = "APR"  
   or    ( T9.ST_APRO_CXPA  = "N"  
           And  T8.TI_SITU != "PAG"))  
   And  T1.CO_EMPR   = TMDOCP_GENE.CO_EMPR  
   And  TMDOCP_GENE.CO_MONE  = T2.CO_MONE  
   And  TMDOCP_GENE.CO_EMPR  = T3.CO_EMPR  
   And   T3.TI_AUXI_EMPR = "P"  
   And  T3.CO_AUXI_EMPR =  TMDOCP_GENE.CO_PROV  
   AND  TMDOCP_GENE.CO_TIPO_DOCU = T5.TI_DOCU  
   AND   TMDOCP_GENE.CO_EMPR = T8.CO_EMPR  
   AND   TMDOCP_GENE.CO_PROV = T8.CO_PROV  
   AND   TMDOCP_GENE.CO_TIPO_DOCU = T8.CO_TIPO_DOCU  
   AND   TMDOCP_GENE.NU_DOCU_PROV = T8.NU_DOCU_PROV  
   And   TMDOCP_GENE.CO_EMPR = T9.CO_EMPR  
GROUP BY TMDOCP_GENE.CO_EMPR, TMDOCP_GENE.CO_MONE, TMDOCP_GENE.CO_PROV   
 ORDER BY  1 , 2, 3')  
  
END   
  
/*OPCION DE SEMANAS*/  
  
IF @ISTI_OPER = 'SEM'  
BEGIN   
  
EXECUTE (' INSERT INTO #TABLA (CO_EMPR , CO_MONE, CO_PROV, CO_ESTA_DOCU, DE_NOMB, DE_MONE, NO_AUXI,  
       IM_0001, IM_0002, IM_0003, IM_0004, IM_0005, IM_0006,   
       IM_0007, IM_0008, IM_0009, IM_0010, IM_0011, IM_0012,  
       IM_0013, IM_0014, IM_0015 )  
 SELECT    
  TMDOCP_GENE.CO_EMPR ,  
  TMDOCP_GENE.CO_MONE ,  
  TMDOCP_GENE.CO_PROV ,  
  MAX(TMDOCP_GENE.CO_ESTA_DOCU),  
  MAX(T1.DE_NOMB),  
  MAX(T2.DE_MONE),  
  MAX(T3.NO_AUXI) ,  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "0")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "1")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "2")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "3")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "4")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "5")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "6")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "7")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "8")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "9")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "10")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "11")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "12")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "13")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))),  
  SUM( PATINDEX(CONVERT(VARCHAR(2), DATEDIFF ( WEEK, Convert(DateTime,"'+@VSFE_VENC+'",103), '+@VSDE_FECH+')), "14")*( ISNULL( T8.IM_PROG, 0) )*(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N")))  
 From  TMDOCP_GENE  ,TMEMPR  T1 , TTMONE T2 , TMAUXI_EMPR T3  ,  TTDOCU_CNTB T5, TDPROG_PAGO T8, TMPARA_TESO T9  
 where  1 = 1 '+@ISCA_WHER_LOCA+'  
   And  '+@VSDE_FECH+' >= Convert(DateTime,"'+@VSFE_VENC+'",103)  
   And  '+@VSDE_FECH+' < dateadd(day,100,Convert(DateTime,"'+@VSFE_VENC+'",103))  
   And   (TMDOCP_GENE.CO_ESTA_DOCU = "ACT"  
   Or   TMDOCP_GENE.CO_ESTA_DOCU = "ACE")  
   And   ( T9.ST_APRO_CXPA  = "S"  
           And  T8.TI_SITU  = "APR"  
   or    ( T9.ST_APRO_CXPA  = "N"  
           And  T8.TI_SITU != "PAG"))  
   And T1.CO_EMPR  =  TMDOCP_GENE.CO_EMPR  
   And TMDOCP_GENE.CO_MONE =  T2.CO_MONE  
   And TMDOCP_GENE.CO_EMPR =  T3.CO_EMPR  
   And    T3.TI_AUXI_EMPR = "P"  
   And T3.CO_AUXI_EMPR =  TMDOCP_GENE.CO_PROV  
   AND  TMDOCP_GENE.CO_TIPO_DOCU = T5.TI_DOCU  
   AND   TMDOCP_GENE.CO_EMPR = T8.CO_EMPR  
   AND   TMDOCP_GENE.CO_PROV = T8.CO_PROV  
   AND   TMDOCP_GENE.CO_TIPO_DOCU = T8.CO_TIPO_DOCU  
   AND   TMDOCP_GENE.NU_DOCU_PROV = T8.NU_DOCU_PROV  
   And   TMDOCP_GENE.CO_EMPR = T9.CO_EMPR  
GROUP BY TMDOCP_GENE.CO_EMPR, TMDOCP_GENE.CO_MONE, TMDOCP_GENE.CO_PROV  
ORDER BY 1, 2, 3')  
  
  
END  
END  
  
  
EXECUTE (' INSERT INTO #TABLA (CO_EMPR , CO_MONE, CO_PROV, CO_ESTA_DOCU, DE_NOMB, DE_MONE, NO_AUXI, CO_TIPO_DOCU, NU_DOCU_PROV,      
       IM_0001, IM_0002, IM_0003, IM_0004, IM_0005, IM_0006,       
       IM_0007, IM_0008, IM_0009, IM_0010, IM_0011, IM_0012,      
      
       IM_0013, IM_0014, IM_0015, IM_SALD )    
  
Select  TMDOCP_GENE.CO_EMPR ,      
  TMDOCP_GENE.CO_MONE ,      
  TMDOCP_GENE.CO_PROV ,      
  TMDOCP_GENE.CO_ESTA_DOCU,      
  T1.DE_NOMB,       
  T2.DE_MONE,      
  T3.NO_AUXI,      
  TMDOCP_GENE.CO_TIPO_DOCU,      
 TMDOCP_GENE.NU_DOCU_PROV,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, (IM_TOTA-IM_PAGA) *(PATINDEX(T5.ST_SIGN, "S") - PATINDEX(T5.ST_SIGN, "N"))      
From   TMDOCP_GENE , TMEMPR  T1, TTMONE T2, TMAUXI_EMPR T3, TTDOCU_CNTB T5, TDPROG_PAGO T8      
Where   1 = 1 '+@ISCA_WHER_LOCA+'       
  And   (TMDOCP_GENE.CO_ESTA_DOCU = "ACT"      
  Or   TMDOCP_GENE.CO_ESTA_DOCU = "ACE")      
   And  T1.CO_EMPR   = TMDOCP_GENE.CO_EMPR      
   And  TMDOCP_GENE.CO_MONE  = T2.CO_MONE      
   And  TMDOCP_GENE.CO_EMPR  = T3.CO_EMPR      
   And   T3.TI_AUXI_EMPR = "P"      
   And  T3.CO_AUXI_EMPR =  TMDOCP_GENE.CO_PROV  
   And  TMDOCP_GENE.CO_TIPO_DOCU = T5.TI_DOCU   
   AND   TMDOCP_GENE.CO_EMPR = T8.CO_EMPR  
   AND   TMDOCP_GENE.CO_PROV = T8.CO_PROV  
   AND   TMDOCP_GENE.CO_TIPO_DOCU = T8.CO_TIPO_DOCU  
   AND   TMDOCP_GENE.NU_DOCU_PROV = T8.NU_DOCU_PROV  
   And  t8.FE_PROG <  Convert(DateTime,"'+@VSFE_VENC+'",103) ')    
     
  
IF @ISTI_REPO='D'      
BEGIN      
SELECT CO_EMPR, CO_MONE, CO_PROV, MAX(CO_ESTA_DOCU), MAX(DE_NOMB),MAX(DE_MONE),      
  MAX(NO_AUXI),       
             SUM(ISNULL( IM_0001, 0) ), SUM(ISNULL(   
IM_0002, 0) ),      
 SUM(ISNULL( IM_0003, 0) ), SUM(ISNULL( IM_0004, 0) ),      
 SUM(ISNULL( IM_0005, 0) ), SUM(ISNULL( IM_0006, 0) ),      
 SUM(ISNULL( IM_0007, 0) ), SUM(ISNULL( IM_0008, 0) ),      
 SUM(ISNULL( IM_0009, 0) ), SUM(ISNULL( IM_0010, 0) ),      
 SUM(ISNULL( IM_0011, 0) ), SUM(ISNULL( IM_0012, 0) ),      
 SUM(ISNULL( IM_0013, 0) ), SUM(ISNULL( IM_0014, 0) ),      
 SUM(ISNULL( IM_0015, 0) ), SUM(ISNULL( IM_SALD, 0) ), CO_TIPO_DOCU, NU_DOCU_PROV      
FROM #TABLA       
GROUP BY CO_EMPR , CO_MONE, CO_PROV, CO_TIPO_DOCU, NU_DOCU_PROV      
HAVING (  SUM(ISNULL( IM_0001, 0) )<>0  OR    SUM(ISNULL( IM_0002, 0) )<>0   OR      
                 SUM(ISNULL( IM_0003, 0) )<>0   OR    SUM(ISNULL( IM_0004, 0) )<>0   OR      
     SUM(ISNULL( IM_0005, 0) )<>0   OR    SUM(ISNULL( IM_0006, 0) )<>0   OR      
     SUM(ISNULL( IM_0007, 0) )<>0   OR    SUM(ISNULL( IM_0008, 0) )<>0   OR      
     SUM(ISNULL( IM_0009, 0) )<>0   OR    SUM(ISNULL( IM_0010, 0) )<>0   OR  
     SUM(ISNULL( IM_SALD, 0) )<>0 )      
ORDER BY CO_EMPR , CO_MONE, CO_PROV, CO_TIPO_DOCU, NU_DOCU_PROV      
END      
      
ELSE      
BEGIN      
SELECT CO_EMPR, CO_MONE, CO_PROV, MAX(CO_ESTA_DOCU), MAX(DE_NOMB),MAX(DE_MONE),      
  MAX(NO_AUXI), SUM(ISNULL( IM_0001, 0) ), SUM(ISNULL( IM_0002, 0) ),      
 SUM(ISNULL( IM_0003, 0) ), SUM(ISNULL( IM_0004, 0) ),      
 SUM(ISNULL( IM_0005, 0) ), SUM(ISNULL( IM_0006, 0) ),      
 SUM(ISNULL( IM_0007, 0) ), SUM(ISNULL( IM_0008, 0) ),      
 SUM(ISNULL( IM_0009, 0) ), SUM(ISNULL( IM_0010, 0) ),      
 SUM(ISNULL( IM_0011, 0) ), SUM(ISNULL( IM_0012, 0) ),      
 SUM(ISNULL( IM_0013, 0) ), SUM(ISNULL( IM_0014, 0) ),      
 SUM(ISNULL( IM_0015, 0) ), SUM(ISNULL( IM_SALD, 0) ), NULL, NULL      
FROM #TABLA       
GROUP BY CO_EMPR , CO_MONE, CO_PROV      
HAVING (  SUM(ISNULL( IM_0001, 0) )<>0  OR    SUM(ISNULL( IM_0002, 0) )<>0   OR      
                 SUM(ISNULL( IM_0003, 0) )<>0   OR    SUM(ISNULL( IM_0004, 0) )<>0   OR      
     SUM(ISNULL( IM_0005, 0) )<>0   OR    SUM(ISNULL( IM_0006, 0) )<>0   OR      
     SUM(ISNULL( IM_0007, 0) )<>0   OR    SUM(ISNULL( IM_0008, 0) )<>0   OR      
     SUM(ISNULL( IM_0009, 0) )<>0   OR    SUM(ISNULL( IM_0010, 0) )<>0   OR      
     SUM(ISNULL( IM_SALD, 0) )<>0 )      
ORDER BY CO_EMPR , CO_MONE, CO_PROV      
END      
/*------------------------- FIN ------------------------------ */


GO
